import { BusinessException } from '../../types/exceptions.js';
import { SysDisable } from '../../enums/system.js';
import { Pool as MySQLPool } from 'mysql2/promise';
import { Pool as PgPool } from 'pg';
import {
  DatabaseOperations,
  DatabaseOperationsFactory,
  BaseMySQLOperations,
  BasePostgreSQLOperations,
} from '../../utils/database-operations.js';
import {
  SysAdminRole,
  CreateSysAdminRoleDto,
  UpdateSysAdminRoleDto,
  SysAdminRoleQueryDto,
} from '../../entities/system/SysAdminRole.js';

// SysAdminRole特定的数据库操作接口
interface SysAdminRoleOperations extends DatabaseOperations<SysAdminRole> {
  getByAdminId(adminId: number): Promise<SysAdminRole[]>;
  getByRoleId(roleId: number): Promise<SysAdminRole[]>;
  deleteByAdminId(adminId: number): Promise<boolean>;
  deleteByRoleId(roleId: number): Promise<boolean>;
}

// MySQL数据库操作实现
class MySQLOperations extends BaseMySQLOperations<SysAdminRole> implements SysAdminRoleOperations {
  constructor(pool: MySQLPool) {
    super(pool);
  }

  async create(adminRole: CreateSysAdminRoleDto): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_admin_role (
                      admin_id, role_id, time, update_time
                  ) VALUES (?, ?, ?, ?)`;
    const params: (string | number)[] = [
      adminRole.admin_id as number,
      adminRole.role_id as number,
      now,
      now,
    ];

    const [result] = await this.pool.execute(sql, params);
    const insertResult = result as { insertId: number };
    return insertResult.insertId;
  }

  async update(id: number, adminRole: UpdateSysAdminRoleDto): Promise<boolean> {
    const updateFields: string[] = [];
    const params: (string | number)[] = [];

    if (adminRole.admin_id !== undefined) {
      updateFields.push('admin_id = ?');
      params.push(adminRole.admin_id as number);
    }
    if (adminRole.role_id !== undefined) {
      updateFields.push('role_id = ?');
      params.push(adminRole.role_id as number);
    }
    if (adminRole.disabled !== undefined) {
      updateFields.push('disabled = ?');
      params.push(adminRole.disabled as number);
    }

    if (updateFields.length === 0) {
      return false;
    }

    updateFields.push('update_time = ?');
    params.push(Date.now());
    params.push(id);

    const sql = `UPDATE sys_admin_role SET ${updateFields.join(', ')} WHERE id = ? AND disabled = 0`;
    const [result] = await this.pool.execute(sql, params);
    const updateResult = result as { affectedRows: number };
    return updateResult.affectedRows > 0;
  }

  async delete(id: number): Promise<boolean> {
    const sql =
      'UPDATE sys_admin_role SET disabled = ?, update_time = ? WHERE id = ? AND disabled = 0';
    const [result] = await this.pool.execute(sql, [SysDisable.DISABLE, Date.now(), id]);
    const updateResult = result as { affectedRows: number };
    return updateResult.affectedRows > 0;
  }

  async getByAdminId(adminId: number): Promise<SysAdminRole[]> {
    const sql = 'SELECT * FROM sys_admin_role WHERE admin_id = ? AND disabled = 0';
    const [rows] = await this.pool.execute(sql, [adminId]);
    return rows as SysAdminRole[];
  }

  async getByRoleId(roleId: number): Promise<SysAdminRole[]> {
    const sql = 'SELECT * FROM sys_admin_role WHERE role_id = ? AND disabled = 0';
    const [rows] = await this.pool.execute(sql, [roleId]);
    return rows as SysAdminRole[];
  }

  async deleteByAdminId(adminId: number): Promise<boolean> {
    const sql =
      'UPDATE sys_admin_role SET disabled = ?, update_time = ? WHERE admin_id = ? AND disabled = 0';
    const [result] = await this.pool.execute(sql, [SysDisable.DISABLE, Date.now(), adminId]);
    const updateResult = result as { affectedRows: number };
    return updateResult.affectedRows > 0;
  }

  async deleteByRoleId(roleId: number): Promise<boolean> {
    const sql =
      'UPDATE sys_admin_role SET disabled = ?, update_time = ? WHERE role_id = ? AND disabled = 0';
    const [result] = await this.pool.execute(sql, [SysDisable.DISABLE, Date.now(), roleId]);
    const updateResult = result as { affectedRows: number };
    return updateResult.affectedRows > 0;
  }

  async getById(id: number): Promise<SysAdminRole | null> {
    const sql = 'SELECT * FROM sys_admin_role WHERE id = ? AND disabled = 0';
    const [rows] = await this.pool.execute(sql, [id]);
    const results = rows as SysAdminRole[];
    return results.length > 0 ? results[0] : null;
  }

  async list(
    page: number = 1,
    pageSize: number = 10,
    conditions: SysAdminRoleQueryDto = {}
  ): Promise<{
    list: SysAdminRole[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    const whereConditions: string[] = ['disabled = 0'];
    const params: (string | number)[] = [];

    if (conditions.admin_id !== undefined) {
      whereConditions.push('admin_id = ?');
      params.push(conditions.admin_id);
    }
    if (conditions.role_id !== undefined) {
      whereConditions.push('role_id = ?');
      params.push(conditions.role_id);
    }

    const whereClause = whereConditions.join(' AND ');
    const countSql = `SELECT COUNT(*) as total FROM sys_admin_role WHERE ${whereClause}`;
    const [countRows] = await this.pool.execute(countSql, params);
    const total = (countRows as { total: number }[])[0].total;

    const offset = (page - 1) * pageSize;
    const listSql = `SELECT * FROM sys_admin_role WHERE ${whereClause} ORDER BY id DESC LIMIT ? OFFSET ?`;
    const [listRows] = await this.pool.execute(listSql, [...params, pageSize, offset]);

    return {
      list: listRows as SysAdminRole[],
      total,
      page,
      pageSize,
    };
  }
}

// PostgreSQL数据库操作实现
class PostgreSQLOperations
  extends BasePostgreSQLOperations<SysAdminRole>
  implements SysAdminRoleOperations
{
  constructor(pool: PgPool) {
    super(pool);
  }

  async create(adminRole: CreateSysAdminRoleDto): Promise<number> {
    const now = Date.now();
    const sql = `INSERT INTO sys_admin_role (
                      admin_id, role_id, time, update_time
                  ) VALUES ($1, $2, $3, $4) RETURNING id`;
    const params: (string | number)[] = [
      adminRole.admin_id as number,
      adminRole.role_id as number,
      now,
      now,
    ];

    const result = await this.queryOne<{ id: number }>(sql, params);
    return result?.id || 0;
  }

  async getById(id: number): Promise<SysAdminRole | null> {
    const sql = 'SELECT * FROM sys_admin_role WHERE id = $1 AND disabled = 0';
    return this.queryOne(sql, [id]);
  }

  async update(id: number, adminRole: UpdateSysAdminRoleDto): Promise<boolean> {
    const fields: string[] = [];
    const params: (string | number)[] = [];
    let paramIndex = 1;

    if (adminRole.admin_id !== undefined) {
      fields.push(`admin_id = $${paramIndex++}`);
      params.push(adminRole.admin_id as number);
    }
    if (adminRole.role_id !== undefined) {
      fields.push(`role_id = $${paramIndex++}`);
      params.push(adminRole.role_id as number);
    }
    if (adminRole.disabled !== undefined) {
      fields.push(`disabled = $${paramIndex++}`);
      params.push(adminRole.disabled as number);
    }

    if (fields.length === 0) {
      return false;
    }

    fields.push(`update_time = $${paramIndex++}`);
    params.push(Date.now());
    params.push(id);

    const sql = `UPDATE sys_admin_role SET ${fields.join(', ')} WHERE id = $${paramIndex} AND disabled = 0`;
    const result = await this.executeUpdate(sql, params);
    return result > 0;
  }

  async delete(id: number): Promise<boolean> {
    const sql =
      'UPDATE sys_admin_role SET disabled = $1, update_time = $2 WHERE id = $3 AND disabled = 0';
    const result = await this.executeUpdate(sql, [SysDisable.DISABLE, Date.now(), id]);
    return result > 0;
  }

  async list(
    page: number,
    pageSize: number,
    conditions: SysAdminRoleQueryDto = {}
  ): Promise<{
    list: SysAdminRole[];
    total: number;
    page: number;
    pageSize: number;
  }> {
    const whereConditions: string[] = ['disabled = 0'];
    const params: (string | number)[] = [];
    let paramIndex = 1;

    if (conditions.admin_id !== undefined) {
      whereConditions.push(`admin_id = $${paramIndex++}`);
      params.push(conditions.admin_id);
    }
    if (conditions.role_id !== undefined) {
      whereConditions.push(`role_id = $${paramIndex++}`);
      params.push(conditions.role_id);
    }

    const whereClause = whereConditions.join(' AND ');

    const countResult = await this.queryOne<{ total: number }>(
      `SELECT COUNT(*) as total FROM sys_admin_role WHERE ${whereClause}`,
      params
    );
    const total = countResult?.total || 0;

    const offset = (page - 1) * pageSize;
    const list = await this.queryMany(
      `SELECT * FROM sys_admin_role WHERE ${whereClause} ORDER BY id DESC LIMIT $${paramIndex++} OFFSET $${paramIndex}`,
      [...params, pageSize, offset]
    );

    return {
      list,
      total,
      page,
      pageSize,
    };
  }

  async getByAdminId(adminId: number): Promise<SysAdminRole[]> {
    const sql = 'SELECT * FROM sys_admin_role WHERE admin_id = $1 AND disabled = 0';
    return this.queryMany(sql, [adminId]);
  }

  async getByRoleId(roleId: number): Promise<SysAdminRole[]> {
    const sql = 'SELECT * FROM sys_admin_role WHERE role_id = $1 AND disabled = 0';
    return this.queryMany(sql, [roleId]);
  }

  async deleteByAdminId(adminId: number): Promise<boolean> {
    const sql =
      'UPDATE sys_admin_role SET disabled = $1, update_time = $2 WHERE admin_id = $3 AND disabled = 0';
    const result = await this.executeUpdate(sql, [SysDisable.DISABLE, Date.now(), adminId]);
    return result > 0;
  }

  async deleteByRoleId(roleId: number): Promise<boolean> {
    const sql =
      'UPDATE sys_admin_role SET disabled = $1, update_time = $2 WHERE role_id = $3 AND disabled = 0';
    const result = await this.executeUpdate(sql, [SysDisable.DISABLE, Date.now(), roleId]);
    return result > 0;
  }
}

export class SysAdminRoleService {
  private dbOperations: SysAdminRoleOperations | null = null;

  constructor() {
    // 延迟初始化数据库操作
  }

  private getDbOperations(): SysAdminRoleOperations {
    if (!this.dbOperations) {
      this.dbOperations = DatabaseOperationsFactory.create<SysAdminRoleOperations>(
        MySQLOperations,
        PostgreSQLOperations
      );
    }
    return this.dbOperations;
  }

  async create(adminRole: CreateSysAdminRoleDto): Promise<number> {
    return await this.getDbOperations().create(adminRole);
  }

  async update(id: number, adminRole: UpdateSysAdminRoleDto): Promise<boolean> {
    return await this.getDbOperations().update(id, adminRole);
  }

  async delete(id: number): Promise<boolean> {
    const adminRole = await this.getDbOperations().getById(id);
    if (!adminRole) {
      throw new BusinessException('管理员角色关联不存在');
    }
    return await this.getDbOperations().delete(id);
  }

  async getByAdminId(adminId: number): Promise<SysAdminRole[]> {
    return await this.getDbOperations().getByAdminId(adminId);
  }

  async getByRoleId(roleId: number): Promise<SysAdminRole[]> {
    return await this.getDbOperations().getByRoleId(roleId);
  }

  async getById(id: number): Promise<SysAdminRole | null> {
    return await this.getDbOperations().getById(id);
  }

  async deleteByAdminId(adminId: number): Promise<boolean> {
    return await this.getDbOperations().deleteByAdminId(adminId);
  }

  async deleteByRoleId(roleId: number): Promise<boolean> {
    return await this.getDbOperations().deleteByRoleId(roleId);
  }

  async list(
    page: number = 1,
    pageSize: number = 10,
    query: SysAdminRoleQueryDto = {}
  ): Promise<{ total: number; list: SysAdminRole[]; page: number; pageSize: number }> {
    return await this.getDbOperations().list(page, pageSize, query);
  }
}
